package dao;

import java.sql.*;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.ArrayList;
import java.util.Map;

import domain.Movie;
import domain.Role;
import domain.User;
import ex.DatabaseException;
import util.*;

public class MovieDAO {

	private Connection conn;
	private PreparedStatement stmt;
	private ResultSet rs;

	private static final String SELECT_MOVIE_BY_MID = "select * from movie where mid = ? ";
	private static final String SELECT_MOVIE_BY_UID = "select * from movie where uid=?";
	private static final String CREATE_MOVIE = "insert into movie (name,genre,year,runTime,summary,poster,linkToMovie,rating,popularity,isCompleted,uid) values (?, ?, ?, ?, ? ,?, ?, ?, ?, ?,?) ";
//	private static final String SELECT_DIRECTORS_BY_MID = "select uid from directed where mid= ? ";
//	private static final String SELECT_ACTORS_BY_MID = "select uid from acted where mid= ? ";
//	private static final String SELECT_CREW_BY_MID = "select uid from worked_as_crew where mid = ? ";
	private static final String UPDATE_MOVIE = "update movie set name = ?, genre = ?, year = ?, summary = ?, runTime = ?, poster = ?,"
			+ " linkToMovie = ?, rating = ?, popularity = ?, isCompleted = ?, timestamp = ? where mid= ? ";
	private static final String DELETE_MOVIE = "DELETE FROM movie WHERE mid = ? ";
	private static final String TOP_MOVIE = "SELECT * FROM movie WHERE isCompleted = 1 ORDER BY rating DESC LIMIT ?";
	private static final String LATEST_MOVIES = "Select  mid from movie WHERE isCompleted = 1 Order BY timestamp desc LIMIT ?";
	private static final String LATEST_ONGOINGS = "Select  mid from movie WHERE isCompleted = 0 Order BY timestamp desc LIMIT ?";
	private static final String LAST_MOVIE = "Select * from movie Order by mid desc limit 1";
	//Lists
	private static final String SELECT_ACTORS_BY_MID = "SELECT * FROM acted WHERE mid = ? AND status = 1";
	private static final String SELECT_ACTORS_APPLIED_BY_MID = "SELECT * FROM acted WHERE mid = ? AND status = 0";
	private static final String SELECT_DIRECTORS_BY_MID = "SELECT uid FROM directed WHERE mid = ?";
	private static final String SELECT_CREW_BY_MID = "SELECT * FROM worked_as_crew WHERE mid = ? AND status=1";
	
	private static final String ALL_MOVIES = "SELECT * FROM movie";
	private static final String ALL_ONGOINGS_ORDER_BY_TIMESTAMP = "SELECT * FROM movie WHERE isCompleted=0 ORDER BY timestamp DESC";
	private static final String SEARCH_MOVIE_BY_GENRE_OR_YEAR = "SELECT * FROM movie WHERE LOWER(genre) = LOWER(?) OR year = ?";
	
	public MovieDAO()
	{
		conn = JDBCUtil.getConnection();
	}
	
	public MovieDAO(Connection conn)
	{
		this.conn = conn;
	}
	
	public LinkedList<Movie> searchMovieByGenreOrYear(String genreText, int yearNo) throws DatabaseException
	{
		LinkedList<Movie> result = new LinkedList<Movie>();
		
		long uid=-1;
		long mid=-1;
		String name = null;
		String genre = null;
		String year = null;
		String summary = null;
		int runTime = -1;
		String poster = null;
		String linkToMovie = null;
		double rating = -1;
		int popularity = -1;
		int isCompleted = -1;
		Timestamp timestamp = null;
		
		try {
			conn = JDBCUtil.dequeueConn();
			stmt = conn.prepareStatement(SELECT_MOVIE_BY_UID);
			stmt.setString(1, genreText);
			stmt.setInt(2, yearNo);
			rs = stmt.executeQuery();
			UserDAO udao = new UserDAO();
			while (rs.next()) {
				mid=rs.getLong("mid");
				uid=rs.getLong("uid");
				name = rs.getString("name");
				genre = rs.getString("genre");
				year = rs.getString("year");
				summary = rs.getString("summary");
				runTime = rs.getInt("runTime");
				poster = rs.getString("poster");
				linkToMovie = rs.getString("linkToMovie");
				rating = rs.getDouble("rating");
				popularity = rs.getInt("popularity");
				isCompleted = rs.getInt("isCompleted");
				timestamp = rs.getTimestamp("timestamp");
				User u = udao.selectUserByUid(uid);
				
				
				result.add(new Movie(mid, u ,name, genre, year, summary, runTime, poster, linkToMovie, rating, popularity,
						isCompleted == 1, timestamp));
			}

			stmt.close();
			JDBCUtil.enqueuConn(conn);
			
		} catch (SQLException e) {
			throw new DatabaseException(
					"Problem with retrieving movie information by uid! "
							+ e.getMessage());
		}
		return result;
	}
	
	public LinkedList<Movie> searchMovieByText(String text) throws DatabaseException
	{
		text = text.toLowerCase();
		LinkedList<Movie> result = new LinkedList<Movie>();
		
		long uid=-1;
		long mid=-1;
		String name = null;
		String genre = null;
		String year = null;
		String summary = null;
		int runTime = -1;
		String poster = null;
		String linkToMovie = null;
		double rating = -1;
		int popularity = -1;
		int isCompleted = -1;
		Timestamp timestamp = null;
		
		UserDAO udao = new UserDAO();
		try {
			conn = JDBCUtil.dequeueConn();
			stmt = conn.prepareStatement(ALL_MOVIES);
			rs = stmt.executeQuery();
			while (rs.next()) {
				
				name = rs.getString("name").toLowerCase();
				summary = rs.getString("summary").toLowerCase();
				
				if (!name.contains(text) && !summary.contains(text))
					continue;
				
				mid=rs.getLong("mid");
				uid=rs.getLong("uid");
				name = rs.getString("name");
				genre = rs.getString("genre");
				year = rs.getString("year");
				summary = rs.getString("summary");
				runTime = rs.getInt("runTime");
				poster = rs.getString("poster");
				linkToMovie = rs.getString("linkToMovie");
				rating = rs.getDouble("rating");
				popularity = rs.getInt("popularity");
				isCompleted = rs.getInt("isCompleted");
				timestamp = rs.getTimestamp("timestamp");
				User u=udao.selectUserByUid(uid);
				
				result.add(new Movie(mid, u ,name, genre, year, summary, runTime, poster, linkToMovie, rating, popularity,
						isCompleted == 1, timestamp));
			}
			stmt.close();
			JDBCUtil.enqueuConn(conn);
		} catch (SQLException e) {
			throw new DatabaseException(
					"Problem with searching movies! "
							+ e.getMessage());
		}
		
		return result;
	}
	
	// Selection of the list of the movies created by a user
	public LinkedList<Movie> selectMovieByUid(long uid)
			throws DatabaseException {
		long mid = -1;
		String name = null;
		String genre = null;
		String year = null;
		String summary = null;
		int runTime = -1;
		String poster = null;
		String linkToMovie = null;
		double rating = -1;
		int popularity = -1;
		int isCompleted = -1;
		Timestamp timestamp = null;
		UserDAO udao = new UserDAO();
		LinkedList<Movie> movielist = new LinkedList<Movie>();
		try {
			conn = JDBCUtil.dequeueConn();
			stmt = conn.prepareStatement(SELECT_MOVIE_BY_UID);
			stmt.setLong(1, uid);
			rs = stmt.executeQuery();
			while (rs.next()) {
				mid = rs.getLong("mid");
				name = rs.getString("name");
				genre = rs.getString("genre");
				year = rs.getString("year");
				summary = rs.getString("summary");
				runTime = rs.getInt("runTime");
				poster = rs.getString("poster");
				linkToMovie = rs.getString("linkToMovie");
				rating = rs.getDouble("rating");
				popularity = rs.getInt("popularity");
				isCompleted = rs.getInt("isCompleted");
				timestamp = rs.getTimestamp("timestamp");
				User u=udao.selectUserByUid(uid);
				movielist.add(new Movie(mid,u ,name, genre, year, summary, runTime, poster,
						linkToMovie, rating, popularity, isCompleted == 1,
						timestamp));
			}

			stmt.close();
			JDBCUtil.enqueuConn(conn);
			
		} catch (SQLException e) {
			throw new DatabaseException(
					"Problem with retrieving movie information by uid! "
							+ e.getMessage());
		}
		return movielist;
	}

	// selecting the information of a movie using Movie ID
	public Movie selectMovieByMid(long mid) throws DatabaseException {
		
		long uid=-1;
		String name = null;
		String genre = null;
		String year = null;
		String summary = null;
		int runTime = -1;
		String poster = null;
		String linkToMovie = null;
		double rating = -1;
		int popularity = -1;
		int isCompleted = -1;
		LinkedList<User> actedList = null;
		LinkedList<User> directedList = null;
		LinkedList<User> crewList = null;
		Timestamp timestamp = null;
		Movie movie = null;
		UserDAO udao = new UserDAO();
		try {
			conn = JDBCUtil.dequeueConn();
			stmt = conn.prepareStatement(SELECT_MOVIE_BY_MID);
			stmt.setLong(1, mid);
			rs = stmt.executeQuery();
			if (rs.next()) {
				uid=rs.getLong("uid");
				name = rs.getString("name");
				genre = rs.getString("genre");
				year = rs.getString("year");
				summary = rs.getString("summary");
				runTime = rs.getInt("runTime");
				poster = rs.getString("poster");
				linkToMovie = rs.getString("linkToMovie");
				rating = rs.getDouble("rating");
				popularity = rs.getInt("popularity");
				isCompleted = rs.getInt("isCompleted");
				timestamp = rs.getTimestamp("timestamp");
			}
			stmt.close();
			/*// selecting directors of the movie
			
			stmt = conn.prepareStatement(SELECT_DIRECTORS_BY_MID);
			stmt.setLong(1, mid);
			rs = stmt.executeQuery();
			directedList = new LinkedList<User>();
			while (rs.next()) {
				directedList.add(udao.selectUserByUid(rs.getLong("uid")));
			}
			stmt.close();
			// selecting actors of the movie
			stmt = conn.prepareStatement(SELECT_ACTORS_BY_MID);
			stmt.setLong(1, mid);
			rs = stmt.executeQuery();
			actedList = new LinkedList<User>();
			while (rs.next()) {
				actedList.add(udao.selectUserByUid(rs.getLong("uid")));
			}
			stmt.close();
			// selecting crews of the movie
			stmt = conn.prepareStatement(SELECT_CREW_BY_MID);
			stmt.setLong(1, mid);
			rs = stmt.executeQuery();
			crewList = new LinkedList<User>();
			while (rs.next()) {
				crewList.add(udao.selectUserByUid(rs.getLong("uid")));
			}
			stmt.close();*/
			JDBCUtil.enqueuConn(conn);
		} catch (SQLException e) {
			throw new DatabaseException(
					"Problem with retrieving movie information by mid! "
							+ e.getMessage());
		}
		User u=udao.selectUserByUid(uid);
		movie = new Movie(mid,u ,name, genre, year, summary, runTime, poster,
				linkToMovie, rating, popularity, isCompleted == 1,
				/*directedList, actedList, crewList,*/ timestamp);
		return movie;
	}

	//returns list of users who directed the movie specified by mid
	public LinkedList<User> selectUsersDirected(long mid) throws DatabaseException
	{	
		LinkedList<User> directors = new LinkedList<User>();
		
//		conn = JDBCUtil.getConnection();
		
		try {
			conn = JDBCUtil.dequeueConn();
			stmt = conn.prepareStatement(SELECT_DIRECTORS_BY_MID);
			stmt.setLong(1, mid);
			
			rs = stmt.executeQuery();
			UserDAO userDAO = new UserDAO();
			while(rs.next())
			{
				long uid = rs.getLong("uid");
				User user = userDAO.selectUserByUid(uid);
				directors.add(user);
			}
			stmt.close();
			JDBCUtil.enqueuConn(conn);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		
		return directors;	
	}
	
	//returns list of role objects(movies w/ actor/character info as in acted database relation/entity) associated w/ movie 
	//specified by mid, 
	public LinkedList<User> selectUsersActedInMovie(long mid) throws DatabaseException
	{
		LinkedList<User> starringList = new LinkedList<User>();
		
//		conn = JDBCUtil.getConnection();
		
		try {
			conn = JDBCUtil.dequeueConn();
			stmt = conn.prepareStatement(SELECT_ACTORS_BY_MID);
			stmt.setLong(1, mid);
			
			rs = stmt.executeQuery();
			UserDAO userDAO = new UserDAO();
			while(rs.next())
			{
				long uid = rs.getLong("uid");
				User user = userDAO.selectUserByUid(uid);
				
				starringList.add(user);
			}
			stmt.close();
			JDBCUtil.enqueuConn(conn);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		
		return starringList;
	}
	
	public LinkedList<Role> selectUsersAppliedForActingInMovie(long mid) throws DatabaseException
	{
		LinkedList<Role> appliedActorsList = new LinkedList<Role>();
		
//		conn = JDBCUtil.getConnection();
		
		try {
			conn = JDBCUtil.dequeueConn();
			stmt = conn.prepareStatement(SELECT_ACTORS_APPLIED_BY_MID);
			stmt.setLong(1, mid);
			
			rs = stmt.executeQuery();
			UserDAO userDAO = new UserDAO();
			MovieDAO movieDAO = new MovieDAO();
			while(rs.next())
			{
				long uid = rs.getLong("uid");
				User user = userDAO.selectUserByUid(uid);
				Movie movie = movieDAO.selectMovieByMid(mid);
				int status = rs.getInt("status");
				String character = rs.getString("character");
				
				Role role = new Role(user, movie, status == 1, character);
				appliedActorsList.add(role);
			}
			stmt.close();
			JDBCUtil.enqueuConn(conn);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		
		return appliedActorsList;
	}
	
	//returns list of users who worked as a crew member in this movie
	public Map<User,String> selectUsersWhoWorkedAsACrewMember(long mid) throws DatabaseException
	{		
		Map<User,String> crew = new HashMap<User,String>();
		
//		conn = JDBCUtil.getConnection();
		
		try {
			conn = JDBCUtil.dequeueConn();
			stmt = conn.prepareStatement(SELECT_CREW_BY_MID);
			stmt.setLong(1, mid);
			
			rs = stmt.executeQuery();
			UserDAO userDAO = new UserDAO();
			while(rs.next())
			{
				long uid = rs.getLong("uid");
				User user = userDAO.selectUserByUid(uid);
				long crewtitle=rs.getLong("crewTitle");
				Title t=TitleUtil.getTitleByTitleBit(crewtitle);
				crew.put(user,t.getTitleName());
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		
		return crew;	
	}	
	
	// Insertion of a new Movie to the database
	public void insertMovie(Movie movie) throws DatabaseException {
		

		try {
			conn = JDBCUtil.dequeueConn();
			stmt = conn.prepareStatement(CREATE_MOVIE);
			stmt.setString(1, movie.getName());
			stmt.setString(2, movie.getGenre());
			stmt.setString(3, movie.getYear());
			stmt.setInt(4, movie.getRunTime());
			stmt.setString(5, movie.getSummary());
			stmt.setString(6, movie.getPoster());
			stmt.setString(7, movie.getLinkToMovie());
			stmt.setDouble(8, movie.getRating());
			stmt.setInt(9, movie.getPopularity());
			stmt.setInt(10, movie.getIsCompleted() ? 1 : 0);
			stmt.setLong(11, movie.getUser()!= null?movie.getUser().getUid() : -1);

			stmt.execute();

			stmt.close();
			JDBCUtil.enqueuConn(conn);
			
		} catch (SQLException e) {
			throw new DatabaseException(
					"Problem with inserting movie information! "
							+ e.getMessage());
		}
	}

	// Editing the Movie fields
	public void updateMovie(Movie mv) throws DatabaseException {
		

		try {
			conn = JDBCUtil.dequeueConn();
			stmt = conn.prepareStatement(UPDATE_MOVIE);
			stmt.setString(1, mv.getName());
			stmt.setString(2, mv.getGenre());
			stmt.setString(3, mv.getYear());
			stmt.setString(4, mv.getSummary());
			stmt.setInt(5, mv.getRunTime());
			stmt.setString(6, mv.getPoster());
			stmt.setString(7, mv.getLinkToMovie());
			stmt.setDouble(8, mv.getRating());
			stmt.setInt(9, mv.getPopularity());
			int i = 0;
			if (mv.getIsCompleted())
				i = 1;
			stmt.setInt(10, i);
			stmt.setTimestamp(11, mv.getTimestamp());
			stmt.setLong(12, mv.getMid());

			stmt.execute();
			
			stmt.close();
			JDBCUtil.enqueuConn(conn);	
		} catch (SQLException e) {
			throw new DatabaseException(
					"Problem with editing movie information! " + e.getMessage());
		}
	}

	// Removal of a movie from the database
	public void deleteMovie(Movie mv) throws DatabaseException {
		

		try {
			conn = JDBCUtil.dequeueConn();
			stmt = conn.prepareStatement(DELETE_MOVIE);
			stmt.setLong(1, mv.getMid());

			stmt.execute();
			stmt.close();
			JDBCUtil.enqueuConn(conn);
		} catch (SQLException e) {
			throw new DatabaseException(
					"Problem with deleting movie information! "
							+ e.getMessage());
		}
	}

	public LinkedList<Movie> selectAllOngoings() throws DatabaseException {
		LinkedList<Movie> allMovies = new LinkedList<Movie>();
		 
		Movie movie = null;
		try {
			conn = JDBCUtil.dequeueConn();
			PreparedStatement stmt = conn.prepareStatement(ALL_ONGOINGS_ORDER_BY_TIMESTAMP);

			ResultSet rs = stmt.executeQuery();
			while (rs.next()) {
				long mid = rs.getLong("mid");
				movie = selectMovieByMid(mid);
				allMovies.add(movie);
			}
			stmt.close();
			JDBCUtil.enqueuConn(conn);
		} catch (SQLException e) {
			throw new DatabaseException("Problem with retrieving all movies! "
					+ e.getMessage());
		}
		return allMovies;
	}
	
	// Selection of the top movies
	public LinkedList<Movie> selectTopMovies(int n) throws DatabaseException {
		LinkedList<Movie> topMovies = new LinkedList<Movie>();
		 

		Movie movie = null;
		try {
			conn = JDBCUtil.dequeueConn();
			PreparedStatement stmt = conn.prepareStatement(TOP_MOVIE);
			stmt.setInt(1, n);

			ResultSet rs = stmt.executeQuery();
			while (rs.next()) {
				long mid = rs.getLong("mid");
				movie = selectMovieByMid(mid);
				topMovies.add(movie);
			}
			stmt.close();
			JDBCUtil.enqueuConn(conn);
		} catch (SQLException e) {
			throw new DatabaseException("Problem with retrieving top movies! "
					+ e.getMessage());
		}
		return topMovies;
	}

	// Selection of the latest movies
	public LinkedList<Movie> selectLatestMovies(int n) throws DatabaseException {
		LinkedList<Movie> latestMovies = new LinkedList<Movie>();
		 

		Movie movie = null;
		try {
			conn = JDBCUtil.dequeueConn();
			PreparedStatement stmt = conn.prepareStatement(LATEST_MOVIES);
			stmt.setInt(1, n);

			ResultSet rs = stmt.executeQuery();
			while (rs.next()) {
				long mid = rs.getLong("mid");
				movie = selectMovieByMid(mid);
				latestMovies.add(movie);
			}
			stmt.close();
			JDBCUtil.enqueuConn(conn);	
		} catch (SQLException e) {
			throw new DatabaseException(
					"Problem with retrieving latest movies! " + e.getMessage());
		}
		return latestMovies;
	}
	
	// Selection of the latest ongoings
	public LinkedList<Movie> selectLatestOngoings(int n) throws DatabaseException {
		LinkedList<Movie> latestOngoings = new LinkedList<Movie>();
		 

		Movie movie = null;
		try {
			conn = JDBCUtil.dequeueConn();
			PreparedStatement stmt = conn.prepareStatement(LATEST_ONGOINGS);
			stmt.setInt(1, n);

			ResultSet rs = stmt.executeQuery();
			while (rs.next()) {
				long mid = rs.getLong("mid");
				movie = selectMovieByMid(mid);
				latestOngoings.add(movie);
			}
			stmt.close();
			JDBCUtil.enqueuConn(conn);	
		} catch (SQLException e) {
			throw new DatabaseException(
					"Problem with retrieving latest ongoings! " + e.getMessage());
		}
		return latestOngoings;
	}

	//Selects last added movie in the system
	public Movie selectLastMovie() throws DatabaseException {
		// lastMovie variable isn't used in anywhere TODO
		Movie lastMovie = null;
		

		Movie movie = null;
		try {
			conn = JDBCUtil.dequeueConn();
			PreparedStatement stmt = conn.prepareStatement(LAST_MOVIE);

			ResultSet rs = stmt.executeQuery();
			if (rs.next()) {
				long mid = rs.getLong("mid");

				movie = selectMovieByMid(mid);
			}
			stmt.close();
			JDBCUtil.enqueuConn(conn);		
		} catch (SQLException e) {
			throw new DatabaseException(
					"Problem with retrieving latest movies! " + e.getMessage());
		}
		return movie;
	}

}